package cs4280.dao;

import cs4280.bean.Booking;
import cs4280.jdbc.DB;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.NamingException;
import javax.sql.DataSource;
import cs4280.bean.*;
import java.util.Iterator;

public class BookingDAO {

    public static enum RESULT {

        OK, DUPLICATE, FAIL
    };

    public ArrayList<Booking> getBookings() {
        ArrayList<Booking> bookings = new ArrayList<Booking>();

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT [id], [memberid], [screeningid], [movieid],[seating],[price],[payment],[request] FROM [booking];");
            while (rs != null && rs.next() != false) {
                Booking booking = new Booking();
                booking.setId(rs.getInt(1));
                booking.setMemberid(rs.getString(2));
                booking.setScreeningid(rs.getInt(3));
                booking.setMovieid(rs.getInt(4));
                booking.setSeating(rs.getString(5));
                booking.setPrice(rs.getInt(6));
                booking.setPayment(rs.getInt(7));
                booking.setRequest(rs.getInt(8));
                bookings.add(booking);
            }
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return bookings;
    }
    
    public Booking getBooking(int bookingid) {
        Booking booking=new Booking();

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();

            PreparedStatement pstmt=conn.prepareStatement("SELECT [id], [memberid], [screeningid], [movieid],[seating],[price],[payment],[request] FROM [booking] where [id] = ? ;");
            pstmt.setInt(1, bookingid);
            ResultSet rs=pstmt.executeQuery();
            while (rs != null && rs.next() != false) {
                
                booking.setId(rs.getInt(1));
                booking.setMemberid(rs.getString(2));
                booking.setScreeningid(rs.getInt(3));
                booking.setMovieid(rs.getInt(4));
                booking.setSeating(rs.getString(5));
                booking.setPrice(rs.getInt(6));
                booking.setPayment(rs.getInt(7));
                booking.setRequest(rs.getInt(8));
            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return booking;
    }

    public boolean isBooked(Screening screening){
        boolean result=false;
        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt=conn.prepareStatement("SELECT * FROM [booking] WHERE [screeningid]=?;");
            pstmt.setInt(1,screening.getId());
           int rows=pstmt.executeUpdate();
            if (rows > 0) {
                result = true;
            } //insert movie fail

            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return result;

    }


    public boolean isBooked(Movie movie){
        boolean result=false;
        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt=conn.prepareStatement("SELECT * FROM [booking] WHERE [movieid]=?;");
            pstmt.setInt(1,movie.getId());
           int rows=pstmt.executeUpdate();
            if (rows > 0) {
                result = true;
            } //insert movie fail

            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return result;

    }
    public RESULT addBookings(ArrayList<Ticket> tickets, int memberid, int payment) {
        Booking booking = null;
        Ticket ticket = null;
        RESULT msg = RESULT.FAIL;
        try {

            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO [booking] ([memberid],[screeningid],[movieid],[seating],[price],[payment],[request]) values (?,?,?,?,?,?,0);");
            pstmt.setInt(1, memberid);
            RESULT result;
            Iterator<Ticket> it = tickets.iterator();
            while (it.hasNext() != false) {
                ticket = it.next();
                pstmt.setInt(2, ticket.getScreeningid());
                pstmt.setInt(3, ticket.getMovieid());
                pstmt.setInt(4, ticket.getSeatingNo());
                pstmt.setInt(5, ticket.getPrice());
                pstmt.setInt(6, payment);

                int rows = pstmt.executeUpdate();

                //successfully add the movie
                if (rows > 0) {
                    result = BookingDAO.RESULT.OK;
                } //insert movie fail
                else {
                    result = BookingDAO.RESULT.FAIL;
                    break;
                }
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }
        return msg;
    }

public ArrayList<Booking> getRefundBookings(int memberid) {
        ArrayList<Booking> bookings = new ArrayList<Booking>();

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT [id], [memberid], [screeningid],[movieid], [seating],[price],[payment],[request] FROM [booking] WHERE [memberid] = ?");
            pstmt.setString(1, String.valueOf(memberid));
            ResultSet rs=pstmt.executeQuery();
            while (rs != null && rs.next() != false) {
                Booking booking = new Booking();
                booking.setId(rs.getInt(1));
                booking.setMemberid(rs.getString(2));
                booking.setScreeningid(rs.getInt(3));
                booking.setMovieid(rs.getInt(4));
                booking.setSeating(rs.getString(5));
                booking.setPrice(rs.getInt(6));
                booking.setPayment(rs.getInt(7));
                booking.setRequest(rs.getInt(8));
                if(rs.getInt(7)!=0)
                    bookings.add(booking);

            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return bookings;
    }

public ArrayList<Booking> getRequestBookings() {
        ArrayList<Booking> bookings = new ArrayList<Booking>();

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT [id], [memberid], [screeningid],[movieid], [seating],[price],[payment],[request] FROM [booking] ;");
            ResultSet rs=pstmt.executeQuery();
            while (rs != null && rs.next() != false) {
                Booking booking = new Booking();
                booking.setId(rs.getInt(1));
                booking.setMemberid(rs.getString(2));
                booking.setScreeningid(rs.getInt(3));
                booking.setMovieid(rs.getInt(4));
                booking.setSeating(rs.getString(5));
                booking.setPrice(rs.getInt(6));
                booking.setPayment(rs.getInt(7));
                booking.setRequest(rs.getInt(8));
                if(rs.getInt(8)==1)
                    bookings.add(booking);

            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return bookings;
    }


public RESULT changeBookingRequest(int request,int bookingid) {
        RESULT msg=BookingDAO.RESULT.FAIL;

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
                  PreparedStatement pstmt=conn.prepareStatement("UPDATE [booking] SET [request]=? WHERE [id]=? ;");
            pstmt.setInt(1, request);
            pstmt.setInt(2, bookingid);
           int rows=pstmt.executeUpdate();
            if (rows > 0) {
                msg = BookingDAO.RESULT.OK;
            } //insert movie fail
            else {
                msg = BookingDAO.RESULT.FAIL;
            }
    
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return msg;
    }

public RESULT rejectRefundRequest(int bookingid) {
        RESULT msg=BookingDAO.RESULT.FAIL;

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt=conn.prepareStatement("UPDATE [booking] SET [request]=0 WHERE [id]=? ;");
            pstmt.setInt(1, bookingid);
           int rows=pstmt.executeUpdate();
            if (rows > 0) {
                msg = BookingDAO.RESULT.OK;
            } //insert movie fail
            else {
                msg = BookingDAO.RESULT.FAIL;
            }

            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return msg;
    }

public RESULT acceptRefundRequest(int bookingid) {
        RESULT msg=BookingDAO.RESULT.FAIL;

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt=conn.prepareStatement("DELETE FROM [booking] WHERE [id]=? ;");
            pstmt.setInt(1, bookingid);
           int rows=pstmt.executeUpdate();
            if (rows > 0) {
                msg = BookingDAO.RESULT.OK;
            } //insert movie fail
            else {
                msg = BookingDAO.RESULT.FAIL;
            }

            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return msg;
    }

}

